<?php
namespace App\Component;

use App\Core\Exception\DefaultException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class Excel
{
    protected $letter = [
        'A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
        'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM', 'N','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
        'BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM', 'N','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ',
    ];
    
    /**
     * 导出excel
     * @param array $header 表头
     * @param array $data 数据
     * @return string 文件路径
     */
    public function download(array $header,array $data)
    {
        $spreadsheet = new Spreadsheet();
        //设置表格
        $spreadsheet->setActiveSheetIndex(0);
        
        //字母表
        $letter = $this->letter;
        
        //设置表头
        $header = array_values($header);
        foreach ($header as $key => $item) {
            $spreadsheet->setActiveSheetIndex(0)->setCellValue($letter[$key].'1',$item);
            //设置表头居中
            $spreadsheet->setActiveSheetIndex(0)->getStyle($letter[$key].'1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }
        
        //设置表格宽度
        /*$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30);*/
        
        $baseRow = 2;//数据从N-1行开始往下输出 这里是避免头信息被覆盖
        
        //遍历数据
        foreach ($data as $i => $row)
        {
            $i = $baseRow;
            $row = array_values($row);
            foreach ($row as $k => $it) {
                $spreadsheet->getActiveSheet()->setCellValue($letter[$k] . $i, $it);
            }
            $baseRow++;
        }
        $writer = IOFactory::createWriter($spreadsheet,'Xls');
        //设置filename
        $filename = \EasySwoole\Utility\Random::makeUUIDV4() . ".xls";
        $path = EASYSWOOLE_ROOT.'/Temp/'.$filename;
        //保存
        $writer->save($path);
        
        return $path;
    }
    
    /**
     * 导入excel获取数据
     * @param $path
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function import($path,$file_max_row = 0){
        $spreadsheet = IOFactory::load($path);
        //读取默认工作表
        $worksheet = $spreadsheet->getSheet(0);
        //取得一共有多少行
        $allRow = $worksheet->getHighestRow();
        if ($file_max_row >0 && $allRow > $file_max_row) {
            throw new DefaultException("行数不能大于{$file_max_row}行，请整理后再上传!");
        }
        //字母表
        $letter = $this->letter;
        //返回数组
        $data = [];
        //表头最大值
        $max_row = 0;
        foreach ($letter as $item) {
            $temp = $spreadsheet->getActiveSheet()->getCell($item.'1')->getValue();
            if ( !empty($temp) ){
                $max_row++;
            }else{
                break;
            }
        }
        
        for($i = 2; $i <= $allRow; $i++)
        {
            $temp = [];
            foreach ($letter as $key => $item) {
                if ( $key >= $max_row ) continue;
                $temp[$item] = trim($spreadsheet->getActiveSheet()->getCell($item.$i)->getValue());
            }
            $data[] = $temp;
        }
        return $data;
    }
    
    /**
     * 导出excel
     * @param array $header 表头
     * @param array $data 数据
     * @return string 文件路径
     */
    public function downloadByXlsWriter(array $header,array $data)
    {
        $config = [
            'path' => EASYSWOOLE_ROOT.'/Temp' // xlsx文件保存路径
        ];
        $excel  = new \Vtiful\Kernel\Excel($config);
        
        //设置filename
        $filename = \EasySwoole\Utility\Random::makeUUIDV4() . ".xlsx";
        
        // fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数
        $filePath = $excel->fileName($filename, 'sheet1')
            ->header($header)
            ->data($data)
            ->output();
        
        // 关闭当前打开的所有文件句柄 并 回收资源
        $excel->close();
        
        return $filePath;
    }
}